﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using EN = class_Entidad;

namespace class_DAO
{
    public class ITEMS_DAO
    {
        /// <summary>
        /// Lista de nombre de grupo
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<EN.ITEMS_EN> ListaGrupo(EN.ITEMS_EN param)
        {
            SqlDataReader sdr;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("LISTA_GRUPO_ITEMS", cnx);
            List<EN.ITEMS_EN> Lista = new List<EN.ITEMS_EN>();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@vID_ITENS", param.ID_ITEMS);
                cmd.Parameters.AddWithValue("@vDESC", param.DESC_ITEMS);

                cnx.Open();
                sdr = cmd.ExecuteReader();
                while (sdr.Read() == true)
                {
                    EN.ITEMS_EN be_Busqueda = new EN.ITEMS_EN();
                    be_Busqueda.ID_ITEMS = sdr.GetInt32(0);
                    be_Busqueda.ID_PADRE = sdr.GetInt32(1);
                    be_Busqueda.DESC_ITEMS = sdr.GetString(2);
                    be_Busqueda.EST_ITEMS = sdr.GetInt32(3);
                    be_Busqueda.DESC_EST_ITEMS = (be_Busqueda.EST_ITEMS == 1) ? "ACTIVO" : "INACTIVO";
                    Lista.Add(be_Busqueda);
                }
                sdr.Close();
                sdr.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Lista;
        }

        /// <summary>
        /// Lista de los Items segun el grupo
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<EN.ITEMS_EN> ListaITEMSGrupo(EN.ITEMS_EN param)
        {
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlDataReader sdr;
            SqlCommand cmd = new SqlCommand("LISTA_ITEMS", cnx);
            List<EN.ITEMS_EN> Lista = new List<EN.ITEMS_EN>();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@vID_ITENS", param.ID_ITEMS);
                cmd.Parameters.AddWithValue("@vID_PADRE", param.ID_PADRE);
                cmd.Parameters.AddWithValue("@vDESC", param.DESC_ITEMS);

                cnx.Open();
                sdr = cmd.ExecuteReader();
                while (sdr.Read() == true)
                {
                    EN.ITEMS_EN be_Busqueda = new EN.ITEMS_EN();
                    be_Busqueda.ID_ITEMS = sdr.GetInt32(0);
                    be_Busqueda.ID_PADRE = sdr.GetInt32(1);
                    be_Busqueda.DESC_PADRE = sdr.GetString(2);
                    be_Busqueda.DESC_ITEMS = sdr.GetString(3);
                    be_Busqueda.EST_ITEMS = sdr.GetInt32(4);
                    be_Busqueda.DESC_EST_ITEMS = (be_Busqueda.EST_ITEMS == 1) ? "ACTIVO" : "INACTIVO";
                    Lista.Add(be_Busqueda);
                }
                sdr.Close();
                sdr.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Lista;
        }

        /// <summary>
        /// Registro de Grupo e Items
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int RegistroITEMS(EN.ITEMS_EN param)
        {
            int Id = 0;

            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlTransaction transaction;            
            SqlCommand cmd = new SqlCommand("INS_ITEMS", cnx);
            //transaction = cnx.BeginTransaction("Registro");
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlPrm = new SqlParameter("@vID_PADRE", SqlDbType.Int);
                sqlPrm.Value = param.ID_PADRE;
                sqlPrm.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm);

                SqlParameter sqlPrm2 = new SqlParameter("@vDESC_ITEMS", SqlDbType.NVarChar);
                sqlPrm2.Value = param.DESC_ITEMS;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                SqlParameter sqlPrm3 = new SqlParameter("@vID_ITEMS", SqlDbType.Int);
                sqlPrm3.Value = param.ID_ITEMS;
                sqlPrm3.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(sqlPrm3);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Id = Convert.ToInt32(cmd.Parameters["@vID_ITEMS"].Value);
                cnx.Close();                
                
            }
            catch (Exception ex)
            {                
                //transaction.Rollback();
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    //transaction.Commit();
                    cnx.Close();
                }
            }

            return Id;
        }

        /// <summary>
        /// Actualiza registro de Items
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int EditarITEMS(EN.ITEMS_EN param)
        {
            int Accion = 0;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("UPD_ITEMS", cnx);   

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlPrm = new SqlParameter("@vID_PADRE", SqlDbType.Int);
                sqlPrm.Value = param.ID_PADRE;
                sqlPrm.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm);

                SqlParameter sqlPrm2 = new SqlParameter("@vDESC_ITEMS", SqlDbType.NVarChar);
                sqlPrm2.Value = param.DESC_ITEMS;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                SqlParameter sqlPrm3 = new SqlParameter("@vEST_ITEMS", SqlDbType.Int);
                sqlPrm3.Value = param.EST_ITEMS;
                sqlPrm3.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm3);

                SqlParameter sqlPrm4 = new SqlParameter("@vID_ITEMS", SqlDbType.Int);
                sqlPrm4.Value = param.ID_ITEMS;
                sqlPrm4.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm4);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Accion =1;
                cnx.Close();                


            }
            catch (Exception ex)
            {
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Accion;
        }

        /// <summary>
        /// Lista de departamento, Provincia, Distrito
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<EN.UBIGEO_EN> listaUbigeo(EN.PARAM_UBIGEO_EN param)
        {
            SqlDataReader sdr;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("LISTA_UBIGE_UBICACION", cnx);
            List<EN.UBIGEO_EN> Lista = new List<EN.UBIGEO_EN>();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@vNIVEL", param.NIVEL);
                cmd.Parameters.AddWithValue("@vCOD_UBIGEO", param.COD_UBIGEO);

                cnx.Open();
                sdr = cmd.ExecuteReader();
                while (sdr.Read() == true)
                {
                    EN.UBIGEO_EN be_Busqueda = new EN.UBIGEO_EN();
                    be_Busqueda.COD_UBIGEO = sdr.GetString(0);
                    be_Busqueda.DESCRIPCION = sdr.GetString(1);
                    Lista.Add(be_Busqueda);
                }
                sdr.Close();
                sdr.Dispose();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return Lista;
        }
    }
}
